CREATE VIEW [dbo].[vContactDetailReport]
AS
SELECT     CR.ContactId, CR.ContactName, CR.ContactSortName, CR.IsInstitute, CR.ContactStatus, CR.TaxIDNumber, CR.NoSolicitationFlag,
                      CR.PreferredAddressCategoryCode, CR.ContactKey, CR.PrimaryMembershipGroupKey, CR.ContactTypeKey, PR.PrefixDesc AS PrefixLong, I.FirstName,
                      I.MiddleName, I.LastName, I.SuffixCode AS SuffixShort, SR.SuffixDesc AS SuffixLong, I.Designation, I.Informal, I.Gender, I.BirthDate,
                      I.PrimaryInstituteName, I.PrimaryInstituteTitle, I.PrimaryInstituteContactKey, CTR.ContactTypeDesc AS ContactType,
                      GM.IsActive AS IsActiveGroupMember, GM.DropDate AS GroupMemberDropDate, GM.JoinDate AS GroupMemberJoinDate,
                      GMD.GroupMemberStatusCode, GMD.EffectiveDate AS GroupMemberEffectiveDate, GMD.ExpirationDate AS GroupMemberExpDate,
                      GMD.TypeSpecificData AS GroupMemberTypeSpecificData, GRR.GroupRoleDesc AS GroupRole, GTR.GroupMemberBranchName, G.GroupKey,
                      I.PrefixCode AS PrefixShort, G.Name AS GroupName, GTR.GroupTypeName AS GroupType
FROM         dbo.vContactReport AS CR LEFT OUTER JOIN
                      dbo.Individual AS I ON CR.ContactKey = I.ContactKey LEFT OUTER JOIN
                      dbo.Institute AS INS ON CR.ContactKey = INS.ContactKey LEFT OUTER JOIN
                      dbo.ContactTypeRef AS CTR ON CR.ContactTypeKey = CTR.ContactTypeKey LEFT OUTER JOIN
                      dbo.SuffixRef AS SR ON I.SuffixCode = SR.SuffixCode LEFT OUTER JOIN
                      dbo.PrefixRef AS PR ON I.PrefixCode = PR.PrefixCode LEFT OUTER JOIN
                      dbo.GroupMain AS G ON CR.PrimaryMembershipGroupKey = G.GroupKey LEFT OUTER JOIN
                      dbo.GroupMember AS GM ON G.GroupKey = GM.GroupKey LEFT OUTER JOIN
                      dbo.GroupMemberDetail AS GMD ON GM.GroupMemberKey = GMD.GroupMemberKey LEFT OUTER JOIN
                      dbo.GroupRoleRef AS GRR ON GMD.GroupRoleKey = GRR.GroupRoleKey LEFT OUTER JOIN
                      dbo.GroupTypeRef AS GTR ON G.GroupTypeKey = GTR.GroupTypeKey

GO